﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Dropthings.DAL
{
    public class UQ_GetWaterTimeDatasInOrNotinWorkTime : BaseQuery
    {
        public UQ_GetWaterTimeDatasInOrNotinWorkTime(string identifyCode, int year, int month, int day, bool isInWorkTime)
            : base()
        {
            StringBuilder sqlStr = new StringBuilder();
            sqlStr.Append("select ReadingTime, isnull(sum(TimeTotalData),0) TimeTotalData FROM DB_WaterTimeDatas");
            sqlStr.AppendFormat(" where readingYear={0}", year);
            sqlStr.AppendFormat(" and ReadingMonth={0}", month);
            sqlStr.AppendFormat(" and ReadingDay={0}", day);
            if (isInWorkTime)
                sqlStr.Append(" and (ReadingTime>=dbo.fun_getWorkStartHour() and ReadingTime<=dbo.fun_getWorkEndHour())");
            else
                sqlStr.Append(" and (ReadingTime<=dbo.fun_getWorkStartHour() or ReadingTime>=dbo.fun_getWorkEndHour())");
            if (identifyCode != "-1")
            {
                sqlStr.AppendFormat(" and IdentifyCode={0}", identifyCode);
            }
            sqlStr.Append(" group by ReadingTime order by ReadingTime");
            _QueryStringName = sqlStr.ToString();
        }
    }

    public class UQ_GetWaterDayDatasInMonth : BaseQuery
    {
        public UQ_GetWaterDayDatasInMonth(string identifyCode, int year, int month)
            : base()
        {
            StringBuilder sqlStr = new StringBuilder();
            sqlStr.Append("SELECT ReadingDay, isnull(SUM(ISNULL(DayTotalData, 0)),0) AS DayTotalData FROM DB_WaterDayDatas");
            sqlStr.AppendFormat(" where ReadingYear ={0}", year);
            sqlStr.AppendFormat(" and ReadingMonth ={0}", month);
            if (identifyCode != "-1")
            {
                sqlStr.AppendFormat(" and IdentifyCode ={0}", identifyCode);
            }
            sqlStr.Append(" group by ReadingDay ORDER BY ReadingDay");
            _QueryStringName = sqlStr.ToString();
        }
    }

    public class UQ_GetWaterMonthDatasInYear : BaseQuery
    {
        public UQ_GetWaterMonthDatasInYear(string identifyCode, int year)
            : base()
        {
            StringBuilder sqlStr = new StringBuilder();
            sqlStr.Append("SELECT ReadingMonth, isnull(SUM(ISNULL(MonthData, 0)),0) AS MonthData FROM DB_WaterMonthDatas");
            sqlStr.AppendFormat(" where ReadingYear ={0}", year);
            if (identifyCode != "-1")
            {
                sqlStr.AppendFormat(" and IdentifyCode ={0}", identifyCode);
            }
            sqlStr.Append(" group by ReadingMonth ORDER BY ReadingMonth");
            _QueryStringName = sqlStr.ToString();
        }
    }

    public class UQ_GetWaterTotalMoneyInYear : BaseQuery
    {
        public UQ_GetWaterTotalMoneyInYear(string identifyCode, int year)
            : base()
        {
            StringBuilder sqlStr = new StringBuilder();
            sqlStr.Append("select isnull(sum(isnull(MonthMoney,0)),0) as Moneys from DB_WaterMonthDatas");
            sqlStr.AppendFormat(" where ReadingYear={0}", year);
            if (identifyCode != "-1")
            {
                sqlStr.AppendFormat(" and IdentifyCode ={0}", identifyCode);
            }
            _QueryStringName = sqlStr.ToString();
        }
    }

    public class UQ_GetWaterMonthMoneyInYear : BaseQuery
    {
        public UQ_GetWaterMonthMoneyInYear(string identifyCode, int year)
            : base()
        {
            StringBuilder sqlStr = new StringBuilder();
            sqlStr.Append("select ReadingMonth,isnull(sum(isnull(MonthMoney,0)),0) as Moneys from DB_WaterMonthDatas");
            sqlStr.AppendFormat(" where ReadingYear={0}", year);
            if (identifyCode != "-1")
            {
                sqlStr.AppendFormat(" and IdentifyCode ={0}", identifyCode);
            }
            sqlStr.Append(" group by ReadingMonth order by ReadingMonth");
            _QueryStringName = sqlStr.ToString();
        }
    }

}
